﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;

/// <summary>
///NewsDao 的摘要说明
/// </summary>
public class NewsDao
{
    DBMM dbm = new DBMM();
    DataTable dt = null;
    string sql = null;
    NewsVo nv = new NewsVo();

    //添加新闻
    public Exception addNews(NewsVo nv)
    {
        sql = "insert into News values(@Nmaintitle,@Nsubhead,@Ncontent,@Ntop,@Tid,@Mid,@Ntime,@Nauthor,@Nposter,@Uid,@Nstate)";
        SqlParameter[] sp = new SqlParameter[] { new SqlParameter("@Nmaintitle", nv.Nmaintitle), 
                                                 new SqlParameter("@Nsubhead", nv.Nsubhead), 
                                                 new SqlParameter("@Ncontent", nv.Ncontent), 
                                                 new SqlParameter("@Ntop", nv.Ntop), 
                                                 new SqlParameter("@Tid", nv.Tid), 
                                                 new SqlParameter("@Mid", nv.Mid),
                                                 new SqlParameter("@Ntime", DateTime.Now.ToString()),
                                                 new SqlParameter("@Nauthor", nv.Nauthor),
                                                 new SqlParameter("@Nposter", nv.Nposter),
                                                 new SqlParameter("@Uid", nv.Uid),
                                                 new SqlParameter("@Nstate", nv.Nstate)};
        Exception ex = null;
        ex = dbm.Execute(sql, sp);
        return ex;
    }

    //删除新闻
    public Exception delNews(string id)
    {
        sql = "delete News where Nid=@Nid";
        SqlParameter[] sp = new SqlParameter[] { new SqlParameter("@Nid", id) };
        Exception ex = null;
        ex = dbm.Execute(sql, sp);
        return ex;
    }

    //查找所有新闻按时间倒序
    public DataTable getNews()
    {
        sql = "select * from News where Nstate=0 order by Nid desc";
        dt = new DataTable();
        dt = dbm.GetTable(sql);
        return dt;
    }

    //查找首页显示新闻信息
    public DataTable getNews(string tid,string count)
    {
        sql = "select top " + count + " * from News left join Topic on News.Tid=Topic.Tid left join Module on News.Mid=Module.Mid where News.Tid=@Tid and Nstate=0 order by Nid desc";
        SqlParameter[] sp = new SqlParameter[] { new SqlParameter("@Tid", tid)};
        dt = new DataTable();
        dt = dbm.GetTable(sql, sp);
        return dt;
    }

    //查找首页显示说三道四信息
    public DataTable getAboutNews(string tid,string mid, string count)
    {
        sql = "select top " + count + " * from News left join Topic on News.Tid=Topic.Tid left join Module on News.Mid=Module.Mid where News.Tid=@Tid and News.Mid=@Mid and Nstate=0 order by Nid desc";
        SqlParameter[] sp = new SqlParameter[] { new SqlParameter("@Tid", tid),
                                                 new SqlParameter("@Mid", mid)};
        dt = new DataTable();
        dt = dbm.GetTable(sql, sp);
        return dt;
    }

    //查找头条新闻信息
    public DataTable getNewsTop()
    {
        sql = "select * from News left join Topic on News.Tid=Topic.Tid left join Module on News.Mid=Module.Mid where Ntop=1 and Nstate=0 order by Nid desc";
        dt = new DataTable();
        dt = dbm.GetTable(sql);
        return dt;
    }

    //查找相关新闻信息
    public DataTable getNews(string tid, string mid, string title)
    {
        if (tid == "0")
        {
            sql = "select * from News left join Topic on News.Tid=Topic.Tid left join Module on News.Mid=Module.Mid where Nmaintitle like @Nmaintitle and Nstate=0 order by Nid desc";
        }
        else
        {
            if (mid == "0")
            {
                sql = "select * from News left join Topic on News.Tid=Topic.Tid left join Module on News.Mid=Module.Mid where Nmaintitle like @Nmaintitle and News.Tid=@Tid and Nstate=0 order by Nid desc";
            }
            else
            {
                sql = "select * from News left join Topic on News.Tid=Topic.Tid left join Module on News.Mid=Module.Mid where Nmaintitle like @Nmaintitle and News.Tid=@Tid and News.Mid=@Mid and Nstate=0 order by Nid desc";
            } 
        }
        SqlParameter[] sp = new SqlParameter[] { new SqlParameter("@Nmaintitle", "%" + title + "%"),
                                                 new SqlParameter("@Tid", tid),
                                                 new SqlParameter("@Mid", mid)};
        dt = new DataTable();
        dt = dbm.GetTable(sql, sp);
        return dt;
    }

    //获得单个新闻信息
    public NewsVo getSingleNews(string id)
    {
        sql = "select * from News left join Topic on News.Tid=Topic.Tid left join Module on News.Mid=Module.Mid where Nid=@Nid";
        SqlParameter[] sp = new SqlParameter[] { new SqlParameter("@Nid", id) };
        dt = new DataTable();
        dt = dbm.GetTable(sql, sp);
        if (dt.Rows.Count > 0)
        {
            nv = new NewsVo();
            nv.Nid = Convert.ToInt32(dt.Rows[0][0]);
            nv.Nmaintitle = dt.Rows[0][1].ToString();
            nv.Nsubhead = dt.Rows[0][2].ToString();
            nv.Ncontent = dt.Rows[0][3].ToString();
            nv.Ntop = Convert.ToInt32(dt.Rows[0][4]);
            nv.Tid = Convert.ToInt32(dt.Rows[0][5]);
            nv.Mid = Convert.ToInt32(dt.Rows[0][6]);
            nv.Ntime = dt.Rows[0][7].ToString();
            nv.Nauthor = dt.Rows[0][8].ToString();
            nv.Nposter = dt.Rows[0][9].ToString();
            nv.Uid = Convert.ToInt32(dt.Rows[0][10]);
            nv.Nstate = Convert.ToInt32(dt.Rows[0][11]);
            return nv;
        }
        else
        {
            return null;
        }
       
    }


    //修改相关新闻信息
    public Exception updateSingleNews(NewsVo nv)
    {
        sql = "update News set Nmaintitle=@Nmaintitle,Nsubhead=@Nsubhead,Ncontent=@Ncontent,Ntop=@Ntop,Tid=@Tid,Mid=@Mid,Nauthor=@Nauthor,Nstate=@Nstate where Nid=@Nid";
        SqlParameter[] sp = new SqlParameter[] { new SqlParameter("@Nmaintitle", nv.Nmaintitle), 
                                                 new SqlParameter("@Nsubhead", nv.Nsubhead), 
                                                 new SqlParameter("@Ncontent", nv.Ncontent),
                                                 new SqlParameter("@Ntop", nv.Ntop), 
                                                 new SqlParameter("@Tid", nv.Tid), 
                                                 new SqlParameter("@Mid", nv.Mid),
                                                 new SqlParameter("@Nauthor", nv.Nauthor),
                                                 new SqlParameter("@Nstate", nv.Nstate),
                                                 new SqlParameter("@Nid", nv.Nid)};
        Exception ex = null;
        ex = dbm.Execute(sql, sp);
        return ex;
    }

    //查找所有主题
    public DataTable getTopic()
    {
        sql = "select * from Topic";
        dt = new DataTable();
        dt = dbm.GetTable(sql);
        return dt;
    }

    //查找相应主题版块
    public DataTable getModule(string tid)
    {
        sql = "select * from Module where Tid=@Tid";
        SqlParameter[] sp = new SqlParameter[] { new SqlParameter("@Tid", tid)};
        dt = new DataTable();
        dt = dbm.GetTable(sql, sp);
        return dt;
    }

    //新闻分页
    public string PageInfo(int count, int pageList, int nowPage, string pageStr, string unit)
    {
        int pageCount = 1;
        string temp = "<font color='red'>"+ "共有" + count + unit + "</font>&nbsp;";
        if (count > 0)
        {
            string firstPage = "";//首页
            string lastPage = "";//上一页
            string nextPage = "";//下一页
            string endPage = "";//未页
            pageCount = count / pageList;
            string selectStr = "跳转：<select name='PageSelect' onchange='window.location.href=value' id='PageSelect'>";//下拉单
            if ((count % pageList) > 0)
            {
                pageCount++;
            }
            for (int i = 1; i <= pageCount; i++)
            {
                if (i == nowPage)
                {
                    selectStr += "<option selected='selected' value='" + pageStr + "page=" + i + "'>" + i + "</option>";
                }
                else
                {
                    selectStr += "<option value='" + pageStr + "page=" + i + "'>" + i + "</option>";
                }
            }
            selectStr += "</select>";
            if (pageCount == 1)
            {
                firstPage = "<font color='#cccccc'>首页</font>";
                lastPage = "<font color='#cccccc'>上页</font>";
                nextPage = "<font color='#cccccc'>下页</font>";
                endPage = "<font color='#cccccc'>尾页</font>";
            }
            else
            {
                if (nowPage == 1)
                {
                    firstPage = "<font color='#cccccc'>首页</font>";
                    lastPage = "<font color='#cccccc'>上页</font>";
                    nextPage = "<a href='" + pageStr + "page=" + (nowPage + 1) + "'>下页</a>";
                    endPage = "<a href='" + pageStr + "page=" + pageCount + "'>尾页</a>";
                }
                else if (nowPage == pageCount)
                {
                    firstPage = "<a href='" + pageStr + "page=1'>首页</a>";
                    lastPage = "<a href='" + pageStr + "page=" + (pageCount - 1) + "'>上页</a>";
                    nextPage = "<font color='#cccccc'>下页</font>";
                    endPage = "<font color='#cccccc'>尾页</font>";
                }
                else
                {
                    nextPage = "<a href='" + pageStr + "page=" + (nowPage + 1) + "'>下页</a>";
                    lastPage = "<a href='" + pageStr + "page=" + (nowPage - 1) + "'>上页</a>";
                    firstPage = "<a href='" + pageStr + "page=1'>首页</a>";
                    endPage = "<a href='" + pageStr + "page=" + pageCount + "'>尾页</a>";
                }
            }
            temp += "&nbsp;&nbsp;" + firstPage + "&nbsp;&nbsp;" + lastPage + "&nbsp;&nbsp;" + nextPage + "&nbsp;&nbsp;" + endPage + "&nbsp;&nbsp;" + selectStr + " " + unit;
        }
        else
        {
            temp = "";
        }
        return temp;
    }

    //查找按类型时间查找新闻信息
    public DataTable getNews(string tid, string mid, string begintime,string endtime)
    {

        if (mid == "0")
        {
            sql = "select * from News left join Topic on News.Tid=Topic.Tid left join Module on News.Mid=Module.Mid where News.Tid=@Tid and Ntime>=@Begintime and Ntime<=@Endtime and Nstate=0 order by Nid desc";
        }
        else
        {
            sql = "select * from News left join Topic on News.Tid=Topic.Tid left join Module on News.Mid=Module.Mid where News.Tid=@Tid and News.Mid=@Mid and Ntime>=@Begintime and Ntime<=@Endtime and Nstate=0 order by Nid desc";
        }
        SqlParameter[] sp = new SqlParameter[] { new SqlParameter("@Begintime", begintime),
                                                     new SqlParameter("@Endtime", endtime),
                                                     new SqlParameter("@Tid", tid),
                                                     new SqlParameter("@Mid", mid)};
        dt = new DataTable();
        dt = dbm.GetTable(sql, sp);
        return dt;
    }

    //热闹集市新闻显示7天内的信息
    public DataTable getBusNews()
    {
        sql = "select * from News left join Topic on News.Tid=Topic.Tid left join Module on News.Mid=Module.Mid where News.Tid=1 and Ntime>='" + DateTime.Now.AddDays(-7) + "' and Nstate=0 order by Nid desc";
        dt = new DataTable();
        dt = dbm.GetTable(sql);
        return dt;
    }

    //查找所有未审核新闻
    public DataTable getUserNews()
    {
        sql = "select * from News left join Topic on News.Tid=Topic.Tid left join Module on News.Mid=Module.Mid where Nstate=1";
        dt = new DataTable();
        dt = dbm.GetTable(sql);
        return dt;
    }

    //审核通过
    public Exception allowNews(string id)
    {
        sql = "update News set Nstate=0 where Nid=@Nid";
        SqlParameter[] sp = new SqlParameter[] { new SqlParameter("@Nid", id) };
        Exception ex = null;
        ex = dbm.Execute(sql, sp);
        return ex;
    }

    //查找单个用户新闻信息
    public DataTable getUserNews(string uid)
    {
        sql = "select * from News left join Topic on News.Tid=Topic.Tid left join Module on News.Mid=Module.Mid where News.Uid=@Uid order by Nid desc";
        SqlParameter[] sp = new SqlParameter[] { new SqlParameter("@Uid", uid) };
        dt = new DataTable();
        dt = dbm.GetTable(sql, sp);
        return dt;
    }
}
